from fastapi import Form
from fastapi import Request
from .config import app, dblock, view_page
from .config import app, view_page


# from .config import dblock
# from .error_view import redirect_error


@app.get("/course")
async def view_student_list(request: Request):
    with dblock() as db:
        db.execute("""
        SELECT sn AS stu_sn, name as stu_name FROM student ORDER BY name
        """)
        students = list(db)

    with dblock() as db:
        db.execute("""
        SELECT sn AS cou_sn, name as cou_name FROM course ORDER BY name
        """)
        courses = list(db)

    with dblock() as db:
        db.execute("""
        SELECT cla_sn, cla_name FROM course_class ORDER BY cla_name
        """)
        classs = list(db)

    with dblock() as db:
        db.execute("""
        SELECT g.stu_sn, g.cou_sn, 
            s.name as stu_name, 
            c.name as cou_name, 
            g.grade 
        FROM course_grade as g
            INNER JOIN student as s ON g.stu_sn = s.sn
            INNER JOIN course as c  ON g.cou_sn = c.sn
        ORDER BY stu_sn, cou_sn;
        """)

        items = list(db)
    return view_page(
        request, "course_list.html", students=students, courses=courses, items=items, classs=classs
    )


@app.post("/course_seach")
async def view_student_list(request: Request,
                            cou_sn: int = Form(...), cla_sn: int = Form(...)):
    # with dblock() as db:
    #     db.execute("""
    #     SELECT sn AS stu_sn, name as stu_name FROM student ORDER BY name
    #     """)
    #     students = list(db)
    #
    # with dblock() as db:
    #     db.execute("""
    #     SELECT sn AS cou_sn, name as cou_name FROM course ORDER BY name
    #     """)
    #     courses = list(db)
    #
    # with dblock() as db:
    #     db.execute("""
    #     SELECT cla_sn, cla_name FROM course_class ORDER BY cla_name
    #     """)
    #     classs = list(db)

    with dblock() as db:
        db.execute("""
            SELECT g.stu_sn, g.cou_sn, 
                s.name as stu_name, 
                c.name as cou_name, 
                cc.cla_name as cla_name, 
                g.grade 
            FROM course_grade as g
                INNER JOIN student as s ON g.stu_sn = s.sn
                INNER JOIN course as c  ON g.cou_sn = c.sn
                INNER JOIN course_class as cc  ON g.cou_sn = cc.cou_sn
            WHERE c.sn = %(cou_sn)s AND cc.cla_sn = %(cla_sn)s
            ORDER BY stu_sn, cou_sn;
            """,
                   dict(cla_sn=cla_sn, cou_sn=cou_sn),
                   )

        items = list(db)

    return view_page(
        # request, "course_list.html", students=students, courses=courses, items=items, classs=classs
        request, "course_list.html", items=items
    )
